﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Business.Entity;
using System.Data;
using System.Data.SqlClient;

namespace Business.Dal
{
    public class SehirProvider:Provider<Sehirler>
    {
        public override bool Insert(Sehirler tObject)
        {
            bool returnValue = false;

            SqlCommand cmd = new SqlCommand("SehirEkle");
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Connection = Tools.Connection();
            Tools.Open();
            cmd.Parameters.Add("UlkeID", SqlDbType.Int).Value = tObject.UlkeID;
            cmd.Parameters.Add("SehirID", SqlDbType.NVarChar).Value = tObject.SehirID;
            tObject.UlkeID = Convert.ToInt32(cmd.ExecuteScalar());
            returnValue = true;
            return returnValue;
        }

        public override bool Update(Sehirler tObject)
        {
            bool returnValue = false;
            SqlCommand cmd = new SqlCommand("SehirGuncelle");
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Connection = Tools.Connection();
            Tools.Open();
            cmd.Parameters.Add("@SehirID", SqlDbType.Int).Value = tObject.SehirID;
            cmd.Parameters.Add("@UlkeID", SqlDbType.Int).Value = tObject.UlkeID;
            cmd.Parameters.Add("@SehirAdi", SqlDbType.NVarChar).Value = tObject.SehirAdi;

            returnValue = Convert.ToBoolean(cmd.ExecuteNonQuery());
            return returnValue;
        }

        public override bool Delete(int RecordID)
        {
            bool returnValue = false;
            SqlCommand cmd = new SqlCommand("SehirSil");
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Connection = Tools.Connection();
            Tools.Open();

            cmd.Parameters.Add("@SehirID", SqlDbType.Int).Value = RecordID;

            returnValue = Convert.ToBoolean(cmd.ExecuteNonQuery());

            return returnValue;
        }

        public List<Sehirler> GetAllRows()
        {
            List<Sehirler> returnValue = new List<Sehirler>();
            SqlCommand cmd = new SqlCommand("SehirlerGetir");
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Connection = Tools.Connection();
            Tools.Open();
            SqlDataReader dr;
            dr = cmd.ExecuteReader();

            while (dr.Read())
            {
                Sehirler Sehir = new Sehirler();
                Sehir.SehirID = dr["SehirID"] is DBNull ? 0 : Convert.ToInt32(dr["SehirID"]);
                Sehir.UlkeID = dr["UlkeID"] is DBNull ? 0 : Convert.ToInt32(dr["UlkeID"]);
                Sehir.SehirAdi = dr["SehirAdi"] is DBNull ? String.Empty : dr["SehirAdi"].ToString();

                returnValue.Add(Sehir);
            }
            dr.Dispose();
            return returnValue;

        }     


        public List<Sehirler> SehirGetir(int UlkeID)
        {
            List<Sehirler> returnValue = new List<Sehirler>();
            SqlCommand cmd = new SqlCommand("SehirGetir");
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@UlkeID", SqlDbType.Int).Value = UlkeID;
            cmd.Connection = Tools.Connection();
            Tools.Open();
            SqlDataReader dr;
            dr = cmd.ExecuteReader();

            while (dr.Read())
            {
                Sehirler sehir = new Sehirler();
                sehir.SehirID = dr["SehirID"] is DBNull ? 0 : Convert.ToInt32(dr["SehirID"]);
                sehir.UlkeID = dr["UlkeID"] is DBNull ? 0 : Convert.ToInt32(dr["UlkeID"]);
                sehir.SehirAdi = dr["SehirAdi"] is DBNull ? String.Empty : dr["SehirAdi"].ToString();

                returnValue.Add(sehir);
            }
            dr.Dispose();
            return returnValue;

        }

        public Sehirler SehirGetirSehirID(int RecordID)
        {
            SqlCommand cmd = new SqlCommand("SehirGetirSehirID");
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@SehirID", RecordID);
            cmd.Connection = Tools.Connection();
            SqlDataReader dr;
            Tools.Open();
            dr = cmd.ExecuteReader();

            dr.Read();
            Sehirler s = new Sehirler();
            s.SehirID = dr["SehirID"] is DBNull ? 0 : Convert.ToInt32(dr["SehirID"]);
            s.UlkeID = dr["UlkeID"] is DBNull ? 0 : Convert.ToInt32(dr["UlkeID"]);
            s.SehirAdi = dr["SehirAdi"] is DBNull ? String.Empty : dr["SehirAdi"].ToString();

            dr.Dispose();

            return s;
        }
    }
}
